package com.lord.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.lord.db.DBUtils;
import com.lord.model.Goddess;

public class GoddessDao {

    public void add(Goddess goddess) throws Exception {
        Connection conn = DBUtils.getConnection();
        String sql = "INSERT INTO imooc_goddess(user_name,sex,age,birthday,email,mobile,create_user,create_date,update_user,update_date,isdel)VALUES(?,?,?,?,?,?,?,current_date(),?,current_date(),?)";
        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setString(1, goddess.getUser_name());
        ptmt.setInt(2, 1);
        ptmt.setInt(3, goddess.getAge());
        ptmt.setDate(4, new Date(goddess.getBirthday().getTime()));
        ptmt.setString(5, goddess.getEmail());
        ptmt.setString(6, goddess.getMobile());
        ptmt.setString(7, goddess.getCreate_user());
        ptmt.setString(8, goddess.getUpdate_user());
        ptmt.setInt(9, goddess.getIsdel());

        ptmt.execute();
    }

    public void update(Goddess goddess) throws SQLException {
        Connection conn = DBUtils.getConnection();
        String sql = "UPDATE imooc_goddess SET user_name=?, sex=?, age=?, birthday=?, email=?, mobile=?, update_user=?, update_date=current_date(), isdel=? WHERE id=? ";
        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setString(1, goddess.getUser_name());
        ptmt.setInt(2, goddess.getSex());
        ptmt.setInt(3, goddess.getAge());
        ptmt.setDate(4, new Date(goddess.getBirthday().getTime()));
        ptmt.setString(5, goddess.getEmail());
        ptmt.setString(6, goddess.getMobile());
        ptmt.setString(7, goddess.getUpdate_user());
        ptmt.setInt(8, goddess.getIsdel());
        ptmt.setInt(9, goddess.getId());

        ptmt.execute();
    }

    public void delete(Integer id) throws SQLException {
        Connection conn = DBUtils.getConnection();
        String sql = "DELETE FROM imooc_goddess WHERE id=?";
        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setInt(1, id);

        ptmt.execute();
    }

    public List<Goddess> select() throws Exception {
        Connection conn = DBUtils.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(
                "SELECT id,user_name,sex,age,birthday,email,mobile,create_user,create_date,update_user,update_date,isdel FROM imooc_goddess");

        List<Goddess> goddessList = new ArrayList<>();
        Goddess goddess = null;

        while (rs.next()) {
            goddess = new Goddess();
            goddess.setId(rs.getInt("id"));
            goddess.setUser_name(rs.getString("user_name"));
            goddess.setAge(rs.getInt("sex"));
            goddess.setSex(rs.getInt("age"));
            goddess.setBirthday(rs.getDate("birthday"));
            goddess.setEmail(rs.getString("email"));
            goddess.setMobile(rs.getString("mobile"));
            goddess.setCreate_user(rs.getString("create_user"));
            goddess.setCreate_date(rs.getDate("create_date"));
            goddess.setUpdate_user(rs.getString("update_user"));
            goddess.setUpdate_date(rs.getDate("update_date"));
            goddess.setIsdel(rs.getInt("isdel"));

            goddessList.add(goddess);
        }

        return goddessList;
    }

    public List<Goddess> select(String name) throws Exception {
        Connection conn = DBUtils.getConnection();
        StringBuilder sb = new StringBuilder();
        sb.append(
                "SELECT id,user_name,sex,age,birthday,email,mobile,create_user,create_date,update_user,update_date,isdel FROM imooc_goddess");
        sb.append(" WHERE user_name=?");

        PreparedStatement ptmt = conn.prepareStatement(sb.toString());
        ptmt.setString(1, name);
        ResultSet rs = ptmt.executeQuery();

        List<Goddess> goddessList = new ArrayList<>();
        Goddess goddess = null;

        while (rs.next()) {
            goddess = new Goddess();
            goddess.setId(rs.getInt("id"));
            goddess.setUser_name(rs.getString("user_name"));
            goddess.setAge(rs.getInt("sex"));
            goddess.setSex(rs.getInt("age"));
            goddess.setBirthday(rs.getDate("birthday"));
            goddess.setEmail(rs.getString("email"));
            goddess.setMobile(rs.getString("mobile"));
            goddess.setCreate_user(rs.getString("create_user"));
            goddess.setCreate_date(rs.getDate("create_date"));
            goddess.setUpdate_user(rs.getString("update_user"));
            goddess.setUpdate_date(rs.getDate("update_date"));
            goddess.setIsdel(rs.getInt("isdel"));

            goddessList.add(goddess);
        }

        return goddessList;
    }

    public List<Goddess> select(List<Map<String, Object>> params) throws Exception {
        Connection conn = DBUtils.getConnection();
        StringBuilder sb = new StringBuilder();
        sb.append(
                "SELECT id,user_name,sex,age,birthday,email,mobile,create_user,create_date,update_user,update_date,isdel FROM imooc_goddess WHERE 1=1");

        if (params != null && params.size() > 0) {
            for (int i = 0; i < params.size(); i++) {
                Map<String, Object> map = params.get(i);
                sb.append(" AND " + map.get("name") + " " + map.get("rela") + " " + map.get("value"));
            }
        }

        PreparedStatement ptmt = conn.prepareStatement(sb.toString());
        ResultSet rs = ptmt.executeQuery();

        List<Goddess> goddessList = new ArrayList<>();
        Goddess goddess = null;

        while (rs.next()) {
            goddess = new Goddess();
            goddess.setId(rs.getInt("id"));
            goddess.setUser_name(rs.getString("user_name"));
            goddess.setAge(rs.getInt("age"));
            goddess.setSex(rs.getInt("sex"));
            goddess.setBirthday(rs.getDate("birthday"));
            goddess.setEmail(rs.getString("email"));
            goddess.setMobile(rs.getString("mobile"));
            goddess.setCreate_user(rs.getString("create_user"));
            goddess.setCreate_date(rs.getDate("create_date"));
            goddess.setUpdate_user(rs.getString("update_user"));
            goddess.setUpdate_date(rs.getDate("update_date"));
            goddess.setIsdel(rs.getInt("isdel"));

            goddessList.add(goddess);
        }

        return goddessList;
    }

    public Goddess get(Integer id) throws SQLException {
        Goddess goddess = null;
        Connection conn = DBUtils.getConnection();
        String sql = "SELECT id,user_name,sex,age,birthday,email,mobile,create_user,create_date,update_user,update_date,isdel FROM imooc_goddess WHERE id=?";
        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setInt(1, id);

        ResultSet rs = ptmt.executeQuery();
        while (rs.next()) {
            goddess = new Goddess();
            goddess.setId(rs.getInt("id"));
            goddess.setUser_name(rs.getString("user_name"));
            goddess.setAge(rs.getInt("age"));
            goddess.setSex(rs.getInt("sex"));
            goddess.setBirthday(rs.getDate("birthday"));
            goddess.setEmail(rs.getString("email"));
            goddess.setMobile(rs.getString("mobile"));
            goddess.setCreate_user(rs.getString("create_user"));
            goddess.setCreate_date(rs.getDate("create_date"));
            goddess.setUpdate_user(rs.getString("update_user"));
            goddess.setUpdate_date(rs.getDate("update_date"));
            goddess.setIsdel(rs.getInt("isdel"));
        }

        return goddess;
    }

}
